using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{

    public partial class VICC_STDMACHINE
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                default:
                    dwReturn["MACHINEID"] = "M_";
                    dwReturn["AMKSTKRS"] = "  ";
                    dwReturn["AMKST"] = "0100";
                    dwReturn["AMTYP"] = "ZT      ";
                    dwReturn["MTYPE"] = "  0100ZT      ";
                    dwReturn["AMVERFKZ"] = "K";
                    dwReturn["SMTYP"] = "0";
                    dwReturn["JMVALUE"] = "0";
                    dwReturn["JJVALUE"] = "0";
                    dwReturn["JOBNUM"] = "0";
                    dwReturn["LASTEND"] = "0";
                    dwReturn["MP01"] = "0";
                    dwReturn["MP02"] = "0";
                    dwReturn["MP03"] = "0";
                    dwReturn["MP04"] = "0";
                    dwReturn["MP05"] = "0";
                    dwReturn["MP06"] = "0";
                    dwReturn["MP07"] = "0";
                    dwReturn["MP08"] = "0";
                    dwReturn["MP09"] = "0";
                    dwReturn["MP10"] = "0";
                    dwReturn["MP11"] = "0";
                    dwReturn["MP12"] = "0";
                    dwReturn["MP13"] = "0";
                    dwReturn["MP14"] = "0";
                    dwReturn["MP15"] = "0";
                    dwReturn["MP16"] = "0";
                    dwReturn["MP17"] = "0";
                    dwReturn["MP18"] = "0";
                    dwReturn["MP19"] = "0";
                    dwReturn["MP20"] = "0";
                    dwReturn["MP21"] = "0";
                    dwReturn["MP22"] = "0";
                    dwReturn["MP23"] = "0";
                    dwReturn["MP24"] = "0";
                    dwReturn["MP25"] = "0";
                    dwReturn["MP26"] = "0";
                    dwReturn["MP27"] = "0";
                    dwReturn["MP28"] = "0";
                    dwReturn["MP29"] = "0";
                    dwReturn["MP30"] = "0";
                    dwReturn["MP31"] = "0";
                    dwReturn["MP32"] = "0";
                    dwReturn["MP33"] = "0";
                    dwReturn["MP34"] = "0";
                    dwReturn["MP35"] = "0";
                    dwReturn["MP36"] = "0";
                    dwReturn["MP37"] = "0";
                    dwReturn["MP38"] = "0";
                    dwReturn["MP39"] = "0";
                    dwReturn["MP40"] = "0";
                    dwReturn["MP41"] = "0";
                    dwReturn["MP42"] = "0";
                    dwReturn["MP43"] = "0";
                    dwReturn["MP44"] = "0";
                    dwReturn["MP45"] = "0";
                    dwReturn["MP46"] = "0";
                    dwReturn["MP47"] = "0";
                    dwReturn["MP48"] = "0";
                    dwReturn["MP49"] = "0";
                    dwReturn["MP50"] = "0"; 
                    break;
            }
        }
        public void InsertICC_STDMACHINE(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_STDMACHINE (MACHINEID,AMKSTKRS,AMKST,AMTYP,MTYPE,AMVERFKZ,SMTYP)"
                        +" SELECT DISTINCT 'M_'||RTRIM(AMHERST) AS MACHINEID, AMKSTKRS,AMKST,AMTYP,AMKSTKRS||AMKST||AMTYP,AMVERFKZ,RTRIM(AMHERST) AS SMTYP "
                        + " FROM AMSTAMM M WHERE AMHERST NOT LIKE '%.%' AND NOT EXISTS (SELECT 1 FROM ICC_STDMACHINE X WHERE 'M_'||RTRIM(AMHERST)=MACHINEID AND X.AMKSTKRS=M.AMKSTKRS AND X.AMKST=M.AMKST ) "
                        + " GROUP BY AMKSTKRS,AMKST,AMTYP,AMVERFKZ,AMHERST";
                    Connect2Proxy(or1);
                    break;
                case 2:
                    or1.CommandText = "INSERT INTO ICC_STDMACHINE ( MACHINEID,AMKSTKRS,AMKST,AMTYP,MTYPE,AMVERFKZ,SMTYP,JMVALUE,JJVALUE,JOBNUM,LASTEND,MP01,MP02,MP03,MP04,MP05,MP06,MP07,MP08,MP09,MP10,MP11,MP12,MP13,MP14,MP15,MP16,MP17,MP18,MP19,MP20,MP21,MP22,MP23,MP24,MP25,MP26,MP27,MP28,MP29,MP30,MP31,MP32,MP33,MP34,MP35,MP36,MP37,MP38,MP39,MP40,MP41,MP42,MP43,MP44,MP45,MP46,MP47,MP48,MP49,MP50)"
                    + " VALUES (:MACHINEID:,:AMKSTKRS:,:AMKST:,:AMTYP:,:MTYPE:,:AMVERFKZ:,:SMTYP:,:JMVALUE:,:JJVALUE:,:JOBNUM:,:LASTEND:,:MP01:,:MP02:,:MP03:,:MP04:,:MP05:,:MP06:,:MP07:,:MP08:,:MP09:,:MP10:,:MP11:,:MP12:,:MP13:,:MP14:,:MP15:,:MP16:,:MP17:,:MP18:,:MP19:,:MP20:,:MP21:,:MP22:,:MP23:,:MP24:,:MP25:,:MP26:,:MP27:,:MP28:,:MP29:,:MP30:,:MP31:,:MP32:,:MP33:,:MP34:,:MP35:,:MP36:,:MP37:,:MP38:,:MP39:,:MP40:,:MP41:,:MP42:,:MP43:,:MP44:,:MP45:,:MP46:,:MP47:,:MP48:,:MP49:,:MP50:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    Connect2Proxy(or1);
                    break;
             } 
        }
        public void DeleteICC_STDMACHINE(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_STDMACHINE";
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM ICC_STDMACHINE WHERE MACHINEID=:MACHINEID:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_STDMACHINE(DataRow dwData, int option = 1)
        {
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = " SELECT K.*,0 AS WD FROM ICC_STDMACHINE K  WHERE  AMKSTKRS=:AMKSTKRS: AND AMKST=:AMKST: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = " SELECT SMTYP,MAX(MP50) AS ABFAGANG,SUBSTR(MKFPLDATV,5,4) AS ABFSOLLDAT, SUM(JP05) AS ABFSOLLMG FROM ICC_MKRF P JOIN AMSTAMM M ON P.ABFBNRKRS=m.AMNRKRS AND P.ABFBLFDNR=M.AMLFDNR AND P.ABFBPOS=M.AMPOS JOIN ICC_STDMACHINE ON RTRIM(AMHERST)=SMTYP "
                        + " WHERE  M.AMKSTKRS=:AMKSTKRS: AND M.AMKST=:AMKST: AND M.AMTYP=:AMTYP:  GROUP BY SMTYP,MKFPLDATV  ORDER BY 1,2";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    or1.CommandText = " SELECT * FROM ICC_STDMACHINE  WHERE  AMKSTKRS=:AMKSTKRS: AND AMKST=:AMKST: AND AMTYP=:AMTYP: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 4:
                    or1.CommandText = " SELECT '00' AS SMTP, JP01 AS ABFAGANG,SUBSTR(MKFPLDATV,5,2) AS ABFSOLLDAT, SUM(JP05) AS ABFSOLLMG FROM ICC_MKRF P JOIN AMSTAMM M ON P.ABFBNRKRS=m.AMNRKRS AND P.ABFBLFDNR=M.AMLFDNR AND P.ABFBPOS=M.AMPOS JOIN ICC_STDMACHINE ON RTRIM(AMHERST)=SMTYP "
                        + " WHERE  M.AMKSTKRS=:AMKSTKRS: AND M.AMKST=:AMKST: AND M.AMTYP=:AMTYP:  GROUP BY JP01,MKFPLDATV  ORDER BY 1,2";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 9999:
                default:
                    or1.CommandText = " SELECT * FROM ICC_STDMACHINE ";
                    break;
            }
            Result = Connect2Proxy(or1);
            Result.Tables[0].TableName = "ICC_STDMACHINE";
            return Result;
        }
        public void UpdateICC_STDMACHINE(DataRow dwData, int options = 1)
        { 
            or1 = OracleDB.CreateCommand();
            switch (options)
            {
                default:
                case 1:
                    or1.CommandText = "UPDATE ICC_STDMACHINE SET MACHINEID=:MACHINEID:,AMKSTKRS=:AMKSTKRS:,AMKST=:AMKST:,AMTYP=:AMTYP:,MTYPE=:MTYPE:,AMVERFKZ=:AMVERFKZ:,SMTYP=:SMTYP:,JMVALUE=:JMVALUE:,JJVALUE=:JJVALUE:,JOBNUM=:JOBNUM:,LASTEND=:LASTEND:,MP01=:MP01:,MP02=:MP02:,MP03=:MP03:,MP04=:MP04:,MP05=:MP05:,MP06=:MP06:,MP07=:MP07:,MP08=:MP08:,MP09=:MP09:,MP10=:MP10:,MP11=:MP11:,MP12=:MP12:,MP13=:MP13:,MP14=:MP14:,MP15=:MP15:,MP16=:MP16:,MP17=:MP17:,MP18=:MP18:,MP19=:MP19:,MP20=:MP20:,MP21=:MP21:,MP22=:MP22:,MP23=:MP23:,MP24=:MP24:,MP25=:MP25:,MP26=:MP26:,MP27=:MP27:,MP28=:MP28:,MP29=:MP29:,MP30=:MP30:,MP31=:MP31:,MP32=:MP32:,MP33=:MP33:,MP34=:MP34:,MP35=:MP35:,MP36=:MP36:,MP37=:MP37:,MP38=:MP38:,MP39=:MP39:,MP40=:MP40:,MP41=:MP41:,MP42=:MP42:,MP43=:MP43:,MP44=:MP44:,MP45=:MP45:,MP46=:MP46:,MP47=:MP47:,MP48=:MP48:,MP49=:MP49:,MP50=:MP50:"
                        + " WHERE MACHINEID=:MACHINEID:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
